package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.*;
import com.hualude.roadsign.dto.MapInfoDto;
import com.hualude.roadsign.model.CoverEntity;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

/**
 * Created by pjn on 2017/8/11.
 */
public interface CoverRepository extends CrudRepository<CoverEntity, Long> {


    /**
     * @Description: 窨井产品DT展示
     * @author xu_jiaHui
     * @date 2017/8/8 15:15
     */
    public List<CoverDto> getCover4DT(DTRequestDto dtRequestDto,
                                      String actorIds,
                                      Integer orgId,
                                      Boolean audit,
                                      String startRoad,
                                      String endRoad,
                                      String roadName) throws Exception;

    /**
     * @Description: 窨井产品DT展示
     * @author xu_jiaHui
     * @date 2017/8/8 15:30
     */
    public Integer getCover4DTCount(DTRequestDto dtRequestDto,
                                    String actorIds,
                                    Integer orgId,
                                    Boolean audit,
                                    String startRoad,
                                    String endRoad,
                                    String roadName) throws Exception;


    /**
     * @Description: 通过ID删除窨井
     * @author xu_jiaHui
     * @date 2017/8/8 20:47
     */
    @Modifying
    @Query(value = "delete from CoverEntity  where coverId=?1")
    public Integer deleteCover(Integer coverId) throws Exception;

    /**
     * @Description: 更新窨井信息
     * @author xu_jiaHui
     * @date 2017/8/9 12:36
     */
    @Modifying
    @Query(value = "update CoverEntity set projectNumber=?2, coverLongitude=?3,coverLatitude=?4,  coverProvince=?5, " +
            " coverCity=?6, coverArea=?7,coverRoadName=?8,coverStartRoad=?9,coverEndRoad=?10,coverShape=?11," +
            "coverMaterial=?12,coverSize=?13,coverPhoto=?14,coverInstallTime=?15,coverInstallOrg=?16,coverPeriod=?17," +
            "coverServiceLife=?18,coverRemark=?19,coverCreatorId=?20,coverModifierId=?21,coverOrgId=?22,coverMainOrgId=?23," +
            "coverIsComplete=0 where coverId=?1")
    public Integer updateCover(Integer coverId,
                               String projectNumber,
                               String coverLongitude,
                               String coverLatitude,
                               String coverProvince,
                               String coverCity,
                               String coverArea,
                               String coverRoadName,
                               String coverStartRoad,
                               String coverEndRoad,
                               Integer coverShape,
                               Integer coverMaterial,
                               String coverSize,
                               String coverPhoto,
                               String coverInstallTime,
                               String coverInstallOrg,
                               Integer coverPeriod,
                               Integer coverServiceLife,
                               String coverRemark,
                               Integer coverCreatorId,
                               Integer coverModifierId,
                               Integer coverOrgId,
                               Integer coverMainOrgId) throws Exception;


    /**
     * @Description: 根据路名获取起始路段
     * @author xu_jiaHui
     * @date 2017/8/9 13:13
     */
    public List<String> getStartRoadByRoadName(String roadName, String actorIds, Integer orgId) throws Exception;

    /**
     * @Description: 根据所在路名 起始路名获取结束路名
     * @author xu_jiaHui
     * @date 2017/8/9 13:27
     */
    public List<String> getEndRoadByStartRoad(String city, String coverRoadName, String startRoad, String actorIds, Integer orgId) throws Exception;

    /**
     * @Description: 获取窨井信息用于列表展示4App
     * @author xu_jiaHui
     * @date 2017/8/9 14:09
     */
    public List<CoverDto> getCover4APP(String actorIds, Integer orgId, Integer mCount, Integer mSize, String city, String startRoad, String endRoad, String roadName) throws Exception;

    /**
     * @Description: 根据Id获取信号机信息
     * @Author: xu_jiaHui
     * @Date: 2017/8/10 16:38
     */
    public List<CoverDto> getCoverById(Integer coverId) throws Exception;

    /**
     * @Description: 获取信号机城市列表4App
     * @Author: xu_jiaHui
     * @Date: 2017/8/10 16:46
     */
    public List<String> getCity4Cover4App(String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception;

    /**
     * @Description: 通过city获取路名列表
     * @Author: xu_jiaHui
     * @Date: 2017/8/10 16:52
     */
    public List<String> getRoadName4CoverByCity(String actorIds, Integer orgId, String city, Integer mCount, Integer mSize) throws Exception;

    /**
     * @Description: 根据 城市 路名 起始路名 获取结束路名
     * @Author: xu_jiaHui
     * @Date: 2017/8/11 19:59
     */
    public List<String> getEndRoadByStartRoad4App(String city, String coverRoadName, String startRoad, String actorIds, Integer orgId, Integer mCount, Integer mSize);

    /**
     * @Description: 根据 城市 路名 获取起始路名
     * @Author: xu_jiaHui
     * @Date: 2017/8/11 20:51
     */
    public List<String> getStartRoadByRoadName4App(String city, String roadName, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception;

    /**
     * @Description: 获取窨井信息用于app端地图展示
     * @Author: xu_jiaHui
     * @Date: 2017/8/11 22:36
     */
    public List<CoverDto> getCover4Map4App(Integer orgId,
                                           String actorIds,
                                           double neLongitude,
                                           double neLatitude,
                                           double swLongitude,
                                           double swLatitude) throws Exception;

    /**
     * @Description: 信号机详情
     * @Author: pan-jianan
     * @Date: 2017/7/22 18:47
     */
    @Query(value = "select new com.hualude.roadsign.dto.CoverFaultDto(c.coverId,c.coverOrgId,c.coverMainOrgId," +
            "c.coverLongitude,c.coverLatitude,c.coverProvince,c.coverCity,c.coverArea,c.coverRoadName,c.coverStartRoad," +
            "c.coverEndRoad,c.coverShape,c.coverMaterial,c.coverSize,c.coverPhoto,c.coverInstallTime,c.coverInstallOrg," +
            "c.coverPeriod,c.coverServiceLife,c.coverCreatorId) " +
            "from CoverEntity c where c.coverId=?1")
    public CoverFaultDto getCoverInfo2Fault(Integer coverId) throws Exception;

    /**
     * @Description: 获取信号机坐标4MAP
     * @Author: pan-jianan
     * @Date: 2017/7/22 15:59
     */
    public List<CoverFaultDto> getCoverCoordinate4Map(Integer orgId, Integer actorId) throws Exception;

    /**
     * @Description: 根据坐标获取信号机信息
     * @Author: pan-jianan
     * @Date: 2017/7/22 22:11
     */
    @Query(value = "select new com.hualude.roadsign.dto.CoverFaultDto(c.coverId,c.coverOrgId,c.coverMainOrgId," +
            "c.coverLongitude,c.coverLatitude,c.coverProvince,c.coverCity,c.coverArea,c.coverRoadName,c.coverStartRoad," +
            "c.coverEndRoad,c.coverShape,c.coverMaterial,c.coverSize,c.coverPhoto,c.coverInstallTime,c.coverInstallOrg," +
            "c.coverPeriod,c.coverServiceLife,c.coverCreatorId) " +
            "from CoverEntity c where c.coverLongitude=?1 and c.coverLatitude=?2")
    public List<CoverFaultDto> getCoverByXY(String longitude, String latitude) throws Exception;

    /**
     * @Description: 信号机DT4故障上报
     * @Author: pan-jianan
     * @Date: 2017/7/22 23:26
     */
    public Integer getCoverFaultReportCount4DT(String search, Integer actorId, Integer orgId, String coverStartRoad, String coverRoadName, String coverEndRoad) throws Exception;

    public List<DTCoverFaultDto> getCoverFaultReport4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId, String coverRoadName, String coverStartRoad, String coverEndRoad) throws Exception;

    /**
     * @Description: 获取第一个路名
     * @Author: pan-jianan
     * @Date: 2017/7/23 0:41
     */
    public List<String> getStartRoad(String roadName, Integer actorId, Integer orgId) throws Exception;

    /**
     * @Description: 根据第一个获取第二个路名
     * @Author: pan-jianan
     * @Date: 2017/7/23 0:41
     */
    public List<String> getSectionByStartRoad(String roadName, Integer actorId, Integer orgId) throws Exception;

    /**
     * @Description: 审核窨井信息
     * @Author: xu_jiaHui
     * @Date: 2017/8/12 18:32
     */
    @Modifying
    @Query(value = "update CoverEntity set coverIsComplete=1 where coverId=?1")
    public Integer auditCover(Integer coverId) throws Exception;

    /**
     * @Description: 获取窨井坐标用于地图显示
     * @Author: xu_jiaHui
     * @Date: 2017/8/13 19:53
     */
    public List<CoverDto> getCoverCoordinates4Map(Integer orgId, Integer orgType, Integer actorId) throws Exception;

    /**
     * @Description: 通过xy获取窨井信息
     * @Author: xu_jiaHui
     * @Date: 2017/8/13 21:27
     */
    @Query(value = "select new com.hualude.roadsign.dto.MapInfoDto(sm.coverId,sm.coverPhoto,sm.projectNumber," +
            "sm.coverProvince,sm.coverCity,sm.coverArea,sm.coverRoadName,sm.coverStartRoad,sm.coverEndRoad,sm.coverSize,sm.coverShape," +
            "sm.coverMaterial,sm.coverInstallTime,sm.coverInstallOrg,sm.coverPeriod,sm.coverServiceLife,sm.coverRemark," +
            "u.userName,o1.orgName,o2.orgName) from CoverEntity sm,OrgEntity o1,OrgEntity o2,UserEntity u where " +
            "o1.orgId=sm.coverOrgId and o2.orgId=sm.coverMainOrgId and sm.coverCreatorId=u.userId and sm.coverLongitude=?1 " +
            "and sm.coverLatitude=?2")
    public MapInfoDto getCoverInfoByXY(String longitude, String latitude) throws Exception;


    /**
     * @Description: 获取窨井数量
     * @Author: xu_jiaHui
     * @Date: 17-11-13 下午7:33
     */

    public Integer getCoverCount(String actorIds, Integer orgId) throws Exception;
}
